package com.dys.dao.impl;

import com.dys.dao.PersonDAO;
import com.dys.entity.Person;
import com.dys.util.DatabaseUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @className: com.dys.dao.impl.PersonDaoImpl
 * @description: 实现PersonDao接口，实现内5个方法
 * @author: pine cone
 * @version: v1.0.0
 * @createTime: 2024/09/12 下午10:52
 */
public class PersonDaoImpl implements PersonDAO {
    @Override
    public int insert(Person person) throws SQLException {
        // 1、获取连接
        Connection conn = DatabaseUtils.getConnection();
        // 2、编写SQL语句
        String sql = "insert into person(name, age,date_of_birth,email,address) values(?,?,?,?,?)";
        // 3、获取PreparedStatement，传入SQL
        PreparedStatement ps = conn.prepareStatement(sql);
        // 4、给占位符赋值
        ps.setString(1, person.getName());
        ps.setInt(2, person.getAge());
        ps.setDate(3, new java.sql.Date(person.getDateOfBirth().getTime()));
        ps.setString(4, person.getEmail());
        ps.setString(5, person.getAddress());
        // 5、执行SQL，获取结果
        int count = ps.executeUpdate();
        // 6、释放资源
        DatabaseUtils.closeAll(conn, ps);
        // 7、返回结果
        return count;
    }

    @Override
    public int updateById(Person person) throws SQLException {
        // 1、获取连接
        Connection conn = DatabaseUtils.getConnection();
        // 2、编写SQL语句
        String sql = "update person set name =?,age=? where id=?";
        // 3、获取PreparedStatement，传入SQL
        PreparedStatement ps = conn.prepareStatement(sql);
        // 4、给占位符赋值
        ps.setString(1, person.getName());
        ps.setInt(2, person.getAge());
        ps.setInt(3, person.getId());
        // 5、执行SQL，获取结果
        int count = ps.executeUpdate();
        // 6、释放资源
        DatabaseUtils.closeAll(conn, ps);
        // 7、返回结果
        return count;
    }

    @Override
    public int deleteById(int id) throws SQLException {
        // 1、获取连接
        Connection conn = DatabaseUtils.getConnection();
        // 2、编写SQL语句
        String sql = "delete from person where id=?";
        // 3、获取PreparedStatement，传入SQL
        PreparedStatement ps = conn.prepareStatement(sql);
        // 4、给占位符赋值
        ps.setInt(1, id);
        // 5、执行SQL，获取结果
        int count = ps.executeUpdate();
        // 6、释放资源
        DatabaseUtils.closeAll(conn, ps);
        // 7、返回结果
        return count;
    }

    @Override
    public Person findById(int id) throws SQLException {
        // 1、获取连接
        Connection conn = DatabaseUtils.getConnection();
        // 2、编写SQL语句
        String sql = "select * from person where id=?";
        // 3、获取PreparedStatement，传入SQL
        PreparedStatement ps = conn.prepareStatement(sql);
        // 4、给占位符赋值
        ps.setInt(1, id);
        // 5、执行SQL，获取结果
        ResultSet rs = ps.executeQuery();
        Person person = null;
        while (rs.next()) {
            person = new Person();
            person.setId(rs.getInt("id"));
            person.setName(rs.getString("name"));
            person.setAge(rs.getInt("age"));
            person.setDateOfBirth(rs.getDate("date_of_birth"));
            person.setEmail(rs.getString("email"));
            person.setAddress(rs.getString("address"));
        }
        // 6、释放资源
        DatabaseUtils.closeAll(conn, ps, rs);
        // 7、返回结果
        return person;
    }

    @Override
    public List<Person> findAll() throws SQLException {
        // 1、获取连接
        Connection conn = DatabaseUtils.getConnection();
        // 2、编写SQL语句
        String sql = "select * from person";
        // 3、获取PreparedStatement，传入SQL
        PreparedStatement ps = conn.prepareStatement(sql);
        // 4、执行SQL，获取结果
        ResultSet rs = ps.executeQuery();
        // 5、处理结果集，疯转数据
        List<Person> list = new ArrayList<Person>();
        Person person = null;
        while (rs.next()) {
            person = new Person();
            person.setId(rs.getInt("id"));
            person.setName(rs.getString("name"));
            person.setAge(rs.getInt("age"));
            person.setDateOfBirth(rs.getDate("date_of_birth"));
            person.setEmail(rs.getString("email"));
            person.setAddress(rs.getString("address"));
            // 记得封装完person，add到前面构建好的List集合中！
            list.add(person);
        }
        // 6、释放资源
        DatabaseUtils.closeAll(conn, ps, rs);
        // 7、返回结果
        return list;
    }
}
